DECLARE
    @segmentXml  XML
,   @documentXml XML
,   @fragmentXml XML
,   @returnXml   XML

SET @segmentXml = 
(
    SELECT
        GroupName = 'Segment'
    ,	Identifier = SegmentId + 0
    ,   SearchString = REPLACE(@Search,'%','')

    ,	SearchResults =
        CAST(
            '<SearchResult name="SegmentName"><![CDATA[' + ISNULL(SegmentName,'') + ']]></SearchResult>'
            +'<SearchResult name="ClientId"><![CDATA[' + ISNULL(ClientId,'') + ']]></SearchResult>'
        AS xml)

    ,   DisplayIdentifier = SegmentName + ''
    FROM
        Segment AS SearchResultGroup
    WHERE
        ISNULL(SegmentName,'') LIKE ISNULL(@Search,'%')
    OR
        ISNULL(ClientId,'') LIKE ISNULL(@Search,'%')

    FOR XML AUTO, ELEMENTS, TYPE
)

SET @documentXml = 
(
    SELECT
        GroupName = 'Document'
    ,   Identifier = DocumentId + 0
    ,   SearchString = REPLACE(@Search,'%','')

    ,	SearchResults =
        CAST(
            '<SearchResult name="Title"><![CDATA[' + ISNULL(Title,'') + ']]></SearchResult>'
            +'<SearchResult name="DocumentShortName"><![CDATA[' + ISNULL(DocumentShortName,'') + ']]></SearchResult>'
            +'<SearchResult name="FileName"><![CDATA[' + ISNULL([FileName],'') + ']]></SearchResult>'
            +'<SearchResult name="Path"><![CDATA[' + ISNULL([Path],'') + ']]></SearchResult>'
            +'<SearchResult name="ClientId"><![CDATA[' + ISNULL(ClientId,'') + ']]></SearchResult>'
            +'<SearchResult name="Tag"><![CDATA[' + ISNULL(Tag,'') + ']]></SearchResult>'
            + ISNULL(dbo.fGetWebXmlString('DocumentKeywordSearch',SearchResultGroup.DocumentId), '')
        AS xml)

    ,   DisplayIdentifier = Title + ''
    FROM
    (
        SELECT
            *
        FROM
            [Document] AS SearchResultGroup
        WHERE
            ISNULL(Title,'') LIKE ISNULL(@Search,'%')
        OR
            ISNULL(DocumentShortName,'') LIKE ISNULL(@Search,'%')
        OR
            ISNULL([FileName],'') LIKE ISNULL(@Search,'%')
        OR
            ISNULL(ClientId,'') LIKE ISNULL(@Search,'%')
        OR
            ISNULL(Tag,'') LIKE ISNULL(@Search,'%')
        OR
            DocumentId IN
            (
                SELECT
                    DocumentId
                FROM
                    WebKeyword
                WHERE
                    KeywordValue LIKE ISNULL(LOWER(@Search),'%')
            )
    )
    AS SearchResultGroup
    FOR XML AUTO, ELEMENTS, TYPE
)

SET @fragmentXml = 
(
    SELECT
        GroupName = 'Fragment'
    ,	Identifier = FragmentId + 0
    ,   SearchString = REPLACE(@Search,'%','')

    ,	SearchResults =
        CAST(
            '<SearchResult name="FragmentName"><![CDATA[' + ISNULL(FragmentName,'') + ']]></SearchResult>'
            +'<SearchResult name="FragmentDisplayName"><![CDATA[' + ISNULL(FragmentDisplayName,'') + ']]></SearchResult>'
            +'<SearchResult name="ItemChar"><![CDATA[' + ISNULL(ItemChar,'') + ']]></SearchResult>'
            +'<SearchResult name="ItemText"><![CDATA[' + ISNULL(Core.dbo.fCharFormat('xmlCDATA',CAST(ItemText AS varchar(max)),DEFAULT,DEFAULT),'') + ']]></SearchResult>'
            +'<SearchResult name="ClientId"><![CDATA[' + ISNULL(ClientId,'') + ']]></SearchResult>'
        AS xml)

    ,   DisplayIdentifier = ISNULL(FragmentDisplayName,FragmentName)
    FROM
        Fragment AS SearchResultGroup
    WHERE
        ISNULL(FragmentName,'') LIKE ISNULL(@Search,'%')
    OR
        ISNULL(FragmentDisplayName,'') LIKE ISNULL(@Search,'%')
    OR
        ISNULL(ItemChar,'') LIKE ISNULL(@Search,'%')
    OR
        ISNULL(ItemText,'') LIKE ISNULL(@Search,'%')
    OR
        ISNULL(ClientId,'') LIKE ISNULL(@Search,'%')

    FOR XML AUTO, ELEMENTS, TYPE
)

SET @returnXml = 
(
    SELECT
        @segmentXml  "node()"
    ,   @documentXml "node()"
    ,   @fragmentXml "node()"
    FOR XML PATH('Search')
)

SELECT @returnXml